﻿

/*****************************************************
AddMessage
*****************************************************/
if exists( select * from dbo.sysobjects where id = object_id(N'AddMessage') and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  drop procedure AddMessage
GO

CREATE PROCEDURE AddMessage
    @id int,
    @msgfrom nvarchar(50),
    @msgto nvarchar(50),
    @msgfromid int,
    @msgtoid int,
    @folder smallint=0,
    @new int=0,
    @subject nvarchar(60),
    @postdatetime datetime,
    @message ntext,
    @savetosentbox smallint=1
AS


INSERT INTO [message] 
	([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
VALUES
	(@msgfrom,@msgfromid,@msgto,@msgtoid,@folder,@new,@subject,@postdatetime,@message)
	
SELECT SCOPE_IDENTITY() AS 'id'

IF @savetosentbox=1 AND @folder=0
	BEGIN
		INSERT INTO [message]
			([msgfrom],[msgfromid],[msgto],[msgtoid],[folder],[new],[subject],[postdatetime],[message])
		VALUES
			(@msgfrom,@msgfromid,@msgto,@msgtoid,1,@new,@subject,@postdatetime,@message)
	END

GO


/*********************************************
DelMessage
*********************************************/	 
if exists ( select * from dbo.sysobjects where id = object_id(N'DelMessage') and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
  drop procedure DelMessage
GO


create procedure DelMessage
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [message] where ' + @condstr
   exec(@strSQL)
GO


/*********************************************************************
GetMessageInfo
*********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetMessageInfo') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetMessageInfo
GO

CREATE PROCEDURE GetMessageInfo
  @id int
AS

SELECT TOP 1
    'Pmid' = [id]
    ,'Msgfrom' = [msgfrom]
    ,'Msgfromid' = [msgfromid]
    ,'Msgto' = [msgto]
    ,'Msgtoid' = [msgtoid]
    ,'Folder' = [folder]
    ,'New' = [new]
    ,'Subject' = [subject]
    ,'Postdatetime' =[postdatetime]
    ,'Message' = [message]
from [message]
where [id] =@id

Go

/***********************************************************************
UpdatePmState
************************************************************************/
IF OBJECT_ID(N'[dbo].[UpdatePmState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdatePmState]
GO

CREATE PROCEDURE [dbo].[UpdatePmState]
	@PmId int,
	@state int
AS

UPDATE [message] SET
	[new] = @state
WHERE
	[id] = @PmId

GO

/**********************************************************************
GetMessageList
**********************************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetMessageList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetMessageList
GO

create procedure GetMessageList
   @pagesize int,
   @pageindex int,
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   
	 set @strSQL = 'select count([id]) from [message]'
	 if @condstr <> ''
	    begin
	    	set @strSQL = @strSQL + ' where ' + @condstr
	    end	    
	 exec(@strSQL)
	 
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Pmid'' = [id]
         ,''Msgfrom'' = [msgfrom]
         ,''Msgfromid'' = [msgfromid]
         ,''Msgto'' = [msgto]
         ,''Msgtoid'' = [msgtoid]
         ,''Folder'' = [folder]
         ,''New'' = [new]
         ,''Subject'' = [subject]
         ,''Postdatetime'' =[postdatetime]
      from 
           [message] '
	if @pageindex > 1

    begin
       set @strSQL = @strSQL + '
         where [id] < 
          ( 
            SELECT   min([id])  
            FROM ( 
               SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
               FROM [message] ' 

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + 'where ' + @condstr
       end

       set @strSQL = @strSQL + 
         ' ORDER BY [id] DESC) AS tblTmp ) '

       if @condstr <> ''
       begin
           set @strSQL = @strSQL + ' and ' + @condstr
       end

   end

   else 
	   begin
	
	      if @condstr <> ''
	        set @strSQL = @strSQL + 'where ' + @condstr
	    
	   end

   set @strSQL = @strSQL  + 'ORDER BY [id] DESC'

   exec(@strSQL)
   
GO